'''
Created on 2019年6月28日

@author: Administrator
'''
import time

import cx_Oracle
import pandas as pd
from sqlalchemy import create_engine

from jenkins_tool.common import EnhanceUtills, const
from jenkins_tool.common.SingleTon import Singleton
from jenkins_tool.log.Logger import Logger

global logger
logger = Logger(clevel=const.Logging_command_level, Flevel=const.Logging_file_level)


class DB_(object):
    def __init__(self, param=''):
        pass

    def get_DB_con(self):
        pass


'''
@author: chejing
@DB:mongodb 
'''


@Singleton
class Mongo_DB(DB_):
    '''
    classdocs
    '''

    def __init__(self, host=const.MONGODB_ip, port=const.MONGODB_port,
                 user_name=const.MONGODB_user_name,
                 user_pwd=const.MONGODB_user_pwd):
        '''
        Constructor
        '''
        DB_.__init__(self, param='')
        self.host = host
        self.port = port
        self.user_name = user_name
        self.user_pwd = user_pwd

        # 建立和数据库系统的连接,指定host及port参数
        client = pg.MongoClient(self.host, self.port)
        db = client.leadDB
        db.authenticate(self.user_name, self.user_pwd)
        self.leadDB = db

    def get_DB_con(self, sql):
        return self.leadDB


"""
@guihehans
定义mysqldb连接方式，使用 sqlalchemy 作为ORM抽象访问 

"""


# @Singleton
class Mysql_DB(DB_):

    def __init__(self, data_base, dialect=const.MYSQL_dialect, driver=const.MYSQL_driver):
        DB_.__init__(self, param='MySQL DB conn init')
        self.dialect = dialect
        self.driver = driver
        self.host = const.MYSQL_CONFIG[data_base]['host']
        self.port = const.MYSQL_CONFIG[data_base]['port']
        self.user = const.MYSQL_CONFIG[data_base]['user']
        self.password = const.MYSQL_CONFIG[data_base]['password']
        self.schema = const.MYSQL_CONFIG[data_base]['schema']
        self.charset = const.MYSQL_CONFIG[data_base]['charset']

    def get_DB_con(self):
        connect_str = "{}+{}://{}:{}@{}:{}/{}?charset={}".format(self.dialect, self.driver,
                                                                 self.user, self.password, self.host,
                                                                 self.port, self.schema, self.charset)
        connect_str_masked = "{}+{}://{}:{}@{}:{}/{}?charset={}".format(self.dialect, self.driver,
                                                                        self.user, "****", self.host,
                                                                        self.port, self.schema, self.charset)
        logger.info('MySQL_DB', 'get_DB_con', 'the connection str is {}'.format(connect_str_masked),
                    'prepare to get conn')
        # use sqlalchemy with a default queue pool integrated already.
        sql_engine = create_engine(connect_str, pool_size=5, max_overflow=0, encoding="utf-8", pool_pre_ping=True)
        db_connection = sql_engine.connect()
        return db_connection

    def do_sql_query(self, sql_str, param_value):
        con_ = self.get_DB_con()
        zz = pd.DataFrame()
        try:
            start_time = time.perf_counter()
            logger.info('MySQL_DB', 'do_sql_query', 'the connection object is {}'.format(id(con_)),
                        'DB pool ready to query')
            con_ = self.get_DB_con()
            frame = pd.read_sql(sql_str, con_, params=param_value)
            end_time = time.perf_counter()
            run_time = end_time - start_time
            logger.info('MySQL_DB', 'do_sql_query',
                        'the connection object is {}. Query finished in {} seconds'.format(id(con_), run_time),
                        'DB pool query finish')
            zz = frame
        except Exception as e:
            logger.info('MySQL_DB', 'do_sql_query', EnhanceUtills.traceback_printer(), ' base service')
            raise e
        finally:
            self.do_con_close(con_)
        return zz

    def do_con_close(self, con_):
        try:
            con_.close()
        except Exception as e:
            logger.info('MySQL_DB', 'do_con_close', EnhanceUtills.traceback_printer(), ' base service')
            raise e


@Singleton
class OracleDB(DB_):

    def __init__(self, user=const.ORACLE_user, port=const.ORACLE_port,
                 pwd=const.ORACLE_password, ip=const.ORACLE_host, db=const.ORACLE_database):
        DB_.__init__(self, param='Oracle_DB init')
        self.user = user
        self.password = pwd
        self.ip = ip
        self.db = db
        self.port = port
        self.dsn = ip + ':' + str(self.port) + "/" + db  # 注意，oracle接受的参数其实是 dsn
        # print( self.dsn)

        # self.pool = pool
        # privatelogger.info('Oracle_DB', 'init', repr(pool), ' base service')

    def get_DB_con(self):
        conn = cx_Oracle.connect(self.user, self.password, self.dsn, encoding="UTF-8", nencoding="UTF-8")
        # privatelogger.info('Oracle_DB', 'get_DB_con', 'the connection object is {}'.format(id(conn)), 'get db connection')
        return conn

    def do_sql_query(self, sql_str, columns):
        con_ = self.get_DB_con()
        curs_ = None
        zz = pd.DataFrame()
        try:
            start_time = time.perf_counter()
            logger.info('Oracle_DB', 'do_sql_query', 'the connection object is {}'.format(id(con_)),
                        'DB pool ready to query')
            curs_ = con_.cursor()
            curs_.execute(sql_str)
            end_time = time.perf_counter()
            run_time = end_time - start_time
            logger.info('Oracle_DB', 'do_sql_query',
                        'the connection object is {}. Query finished in {} seconds'.format(id(con_), run_time),
                        'DB pool query finish')
            rows = curs_.fetchall()

            zz = pd.DataFrame(rows, columns=columns)
        except Exception as e:
            logger.info('Oracle_DB', 'do_sql_query', EnhanceUtills.traceback_printer(), ' base service')
            raise e
        finally:
            self.do_con_close(con_, curs_)
        return zz

    def do_con_close(self, con_, curs_):
        try:
            if curs_ is not None:
                curs_.close()
            if con_ is not None:
                con_.close()
        except Exception as e:
            logger.info('Oracle_DB', 'do_con_close', EnhanceUtills.traceback_printer(), ' base service')
            raise e


'''
@author: chejing
用于生成sql文
'''


class SQL_creater(object):
    def __init__(self):
        pass

    def create_query_sql_oracle(self, table_name, quert_column, condition_column, stock_code=''):
        str_sql = "select "
        quert_column_tmp = []
        for i in quert_column:
            quert_column_tmp.append('t.' + i)
        str_sql = str_sql + ','.join(quert_column_tmp)

        str_sql = str_sql + " from " + table_name + " t "
        if stock_code != '':
            str_sql = str_sql + " where t." + condition_column + "='" + str(stock_code).zfill(6) + "'"

        return str_sql


# str_ = SQL_creater().create_query_sql_oracle('test_tb', ['t1','t2','t3'], 'code', '000001')
# print(str_)


# test_l = ['217025']
# sql_str = "select t.FUND_CODE, t.WORKDAY, t.NAV, t.ACCU_UNIT_NAV, t.ROSE from N_NAV_DAY t where t.FUND_CODE = '"+test_l[0] +"'"
# t_df = Oracle_DB().do_sql_query(sql_str, ['FUND_CODE','WORKDAY','NAV','ACCU_UNIT_NAV','ROSE'])
# print(list(t_df.head(1)['ACCU_UNIT_NAV'])[0] is None)
# print(t_df)
# str_sql = "SELECT t.zipcode, t.phone  FROM F_FUND_COMP t where t.comp_id ='2088A1E663'" 
# #     print(k.do_sql_query(sql_str=str_sql,columns=['test','phone']))        
# conn = Oracle_DB().get_DB_con()
# curs= conn.cursor()
# curs.execute(str_sql)
# row=curs.fetchall()
# print(row)
# 
# for i in range(10):
#     k = Oracle_DB()
#     str_sql = "SELECT t.zipcode, t.phone  FROM F_FUND_COMP t where t.comp_id ='2088A1E663'" 
#     (k.do_sql_query(sql_str=str_sql,columns=['test','phone']))
#           
#        
# self.conn = Oraclepool.get_conn(self)
# conn = cx_Oracle.connect('mfund/mfund@10.1.1.222:1521/orcl')
# print(conn)
# curs=conn.cursor()
# sql="SELECT t.zipcode, t.phone  FROM F_FUND_COMP t where t.comp_id ='2088A1E663'" #sql语句
# curs.execute (sql)
# row=curs.fetchall()
# print(row)
# 
# zz=pd.DataFrame(row,columns=['test','phone']); 
# print(zz)
# curs.close()
# conn.close()

# if __name__ == "__main__":
#     # table_name=parameters_conf.STOCK_DATA_TABLE
#     # i='000001'
#     # sql_str = SQL_creater().create_query_sql_oracle(table_name, parameters_conf.STOCK_QUERY_COLUMN,
#     #                                                                     'SHORT_CODE', i)
#     sub_df = Oracle_DB().do_sql_query(sql_str, parameters_conf.STOCK_QUERY_COLUMN)
#     # print(sub_df)
#     pass

# Oracle_DB()

if __name__ == "__main__":
    # sql_str_ = """SELECT customized_id,value FROM custom_values WHERE customized_id =%s  AND custom_field_id ='18'"""
    # database = 'redmine'
    # value = [28666]
    # sub_df = Mysql_DB(database).do_sql_query(sql_str_, value)
    # print(sub_df)
    connect_str = "mysql+pymysql://redmine:redmine@10.1.1.135:3306/redmine?charset=utf8"
    logger.info('MySQL_DB', 'get_DB_con', 'the connection str is {}'.format(connect_str),
                'prepare to get conn')
    sql_engine = create_engine(connect_str, encoding="utf-8")
    db_connection = sql_engine.connect()
    print(db_connection)
    # from MySQLdb import _mysql
    # db=_mysql.connect(host="10.1.1.99",port=3306,user="redmine",
    #               passwd="redmine",db="redmine")
